package com.situ.company.score.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.company.score.model.ScoreModel;

public class ScoreDao {

    public int insert(ScoreModel model) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/st_company?useSSL=false&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true&serverTimezone=Hongkong";
        String user = "root";
        String pass = "";
        Connection conn = DriverManager.getConnection(url, user, pass);
        String sql = "insert into score(code_emp,code_pro,score) values(?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, model.getCode_emp());
        ps.setString(2, model.getCode_pro());
        ps.setString(3, model.getScore());
        int count = ps.executeUpdate();
        ps.close();
        conn.close();
        return count;
    }

    public int delete(ScoreModel model) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/st_company?useSSL=false&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true&serverTimezone=Hongkong";
        String user = "root";
        String pass = "";
        Connection conn = DriverManager.getConnection(url, user, pass);
        String sql = "delete from score where code_emp = ? and code_pro = ? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, model.getCode_emp());
        ps.setString(2, model.getCode_pro());
        int count = ps.executeUpdate();
        ps.close();
        conn.close();
        return count;
    }

    public int update(ScoreModel model) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/st_company?useSSL=false&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true&serverTimezone=Hongkong";
        String user = "root";
        String pass = "";
        Connection conn = DriverManager.getConnection(url, user, pass);
        String sql = "update score set score=? where code_emp = ? and code_pro = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, model.getScore());
        ps.setString(2, model.getCode_emp());
        ps.setString(3, model.getCode_pro());
        int count = ps.executeUpdate();
        ps.close();
        conn.close();
        return count;
    }

    public ScoreModel selectByCode(ScoreModel model) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/st_company?useSSL=false&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true&serverTimezone=Hongkong";
        String user = "root";
        String pass = "";
        Connection conn = DriverManager.getConnection(url, user, pass);
        String sql = "select code_emp,code_pro,score from score where code_emp = ? and code_pro = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, model.getCode_emp());
        ps.setString(2, model.getCode_pro());
        ResultSet rs = ps.executeQuery();
        ScoreModel dbModel = null;
        while (rs.next()) {
            String code_emp = rs.getString("code_emp");
            String code_pro = rs.getString("code_pro");
            String score = rs.getString("score");
            dbModel = new ScoreModel();
            dbModel.setCode_emp(code_emp);
            dbModel.setCode_pro(code_pro);
            dbModel.setScore(score);
        }
        rs.close();
        ps.close();
        conn.close();
        return dbModel;
    }

    public List<ScoreModel> selectList(ScoreModel model) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/st_company?useSSL=false&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true&serverTimezone=Hongkong";
        String user = "root";
        String pass = "";
        Connection conn = DriverManager.getConnection(url, user, pass);
        String sql = "select code_emp,code_pro,score from score where code_emp like ? and code_pro like ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, "%" + model.getCode_emp() + "%");
        ps.setString(2, "%" + model.getCode_pro() + "%");
        ResultSet rs = ps.executeQuery();
        List<ScoreModel> list = new ArrayList<>();
        while (rs.next()) {
            String code_emp = rs.getString("code_emp");
            String code_pro = rs.getString("code_pro");
            String score = rs.getString("score");
            ScoreModel dbModel = new ScoreModel();
            dbModel.setCode_emp(code_emp);
            dbModel.setCode_pro(code_pro);
            dbModel.setScore(score);
            list.add(dbModel);
        }
        rs.close();
        ps.close();
        conn.close();
        return list;
    }

}
